package cn.dglydrpy.study.j2ee.manage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.dglydrpy.study.j2ee.jdbc.utils.DBUtil;
import cn.dglydrpy.study.j2ee.manage.entity.User;

/*
 * DAO：封装了数据访问逻辑的对象
 * 
 CREATE TABLE t_user(
 	id int(11) primary key auto_increment,
 	username varchar(255) not null,
 	password varchar(255) not null,
 	email varchar(255),
 	phone varchar(255),
 	age int(3),
 	gender char(1)
 )engine=InnoDB character set=utf8 collate=utf8_general_ci;

 */
public class UserDAO {

	//插入
	public void save(User user){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBUtil.getConn();
			String sql = "insert into t_user values(null,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getPhone());
			ps.setInt(5, user.getAge());
			ps.setString(6, user.getGender());
			ps.executeUpdate();
			System.out.println("保存成功!");
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			try {
				DBUtil.close(conn, ps, null);
			} catch (Exception e){
				e.printStackTrace();
			}
		}
	}


	//查询所有
	public List<User> findAll() {
		List<User> users = new ArrayList<User>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			String sql = "select * from t_user";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setPhone(rs.getString(5));
				user.setAge(rs.getInt(6));
				user.setGender(rs.getString(7));
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);//此时可以不需要throws 若直接抛出必须throws
		} finally {
			try {
				DBUtil.close(conn, ps, rs);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return users;
	}

	//通过列表上的删除键删除，通过主键找到要删的具体对象
	public void deleteById(int id){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBUtil.getConn();
			String sql = "delete from t_user where id= ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			System.out.println("删除成功!");
	
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				DBUtil.close(conn, ps, null);
			} catch (Exception e){
				e.printStackTrace();
			}
		}
	}
	
	//根据用户名查询用户所有信息
	public User findByUsername(String username){
		User user = new User();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			String sql = "select * from t_user where username=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			if(rs==null){
				return null;
			}
			while(rs.next()){
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setPhone(rs.getString(5));
				user.setAge(rs.getInt(6));
				user.setGender(rs.getString(7));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			try {
				DBUtil.close(conn,ps, rs);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return user;
	}
	
	public void update(User user){
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			//准备sql 需要拼接进来的变量用?代替
			String sql = "update t_user set username=?,password=?,email=?,phone=?, age=?, gender=? where id=?";
			stat = conn.prepareStatement(sql);
			stat.setString(1, user.getUsername());
			stat.setString(2, user.getPassword());
			stat.setString(3, user.getEmail());
			stat.setString(4, user.getPhone());
			stat.setInt(5, user.getAge());
			stat.setString(6, user.getGender());
			stat.setInt(7, user.getId());
			stat.executeUpdate();
			System.out.println("修改成功!");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, rs);
		}
	}
}